package com.eling.elcms.cater.dao.hibernate;

import com.eling.elcms.cater.dao.IMealRecordDao;
import com.eling.elcms.cater.dao.TravelAccountFlowDao;
import com.eling.elcms.cater.model.MealRecord;
import com.eling.elcms.cater.model.TravelAccountFlow;
import com.eling.elcms.cater.model.view.DishView;
import com.eling.elcms.cater.model.view.MealRecordDetailView;
import com.eling.elcms.cater.model.view.MealRecordView;
import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.core.util.PropertyUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class MealRecordDaoHibernate extends GenericDaoHibernate<MealRecord, Long> implements IMealRecordDao{
	
	@Autowired
	private TravelAccountFlowDao travelAccountFlowDao;
	
	public MealRecordDaoHibernate() {
		super(MealRecord.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MealRecordView> queryAll(Date startDate, Date endDate) {
		StringBuffer sql = new StringBuffer();
		sql.append("  SELECT bp.`name`,bp.idNumber,bp.sex,sum(ccaf.money),cca.pkTravelAccount,bp.pkPersonalInfo   ");
		sql.append("  FROM cate_travelaccountflow ccaf                                                            ");
		sql.append("  LEFT JOIN cate_travelaccount cca ON cca.pkTravelAccount=ccaf.pkTravelAccount       		");
		sql.append("  LEFT JOIN travel_roompersonalInfo trp ON trp.pkRoomPersonalInfo = cca.pkRoomPersonalInfo 		");
		sql.append("  LEFT JOIN bd_personalinfo bp ON bp.pkPersonalInfo = trp.pkPersonalInfo                  		");
		sql.append("  WHERE ccaf.operateDate BETWEEN :startDate and :endDate   								 		");
		sql.append("  AND ccaf.consumeType='"+TravelAccountFlow.ConsumeType.Consume+"' 							");
		sql.append("  GROUP BY bp.pkPersonalInfo                                                              		");
		
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("startDate", startDate).
								setParameter("endDate", endDate).list();
		List<MealRecordView> viewList = new ArrayList<>();
		for (int i = 0; i < objList.size(); i++) {
			MealRecordView mealRecordView = new MealRecordView();
			Object[] objects = objList.get(i);
			mealRecordView.setName(objects[0]==null?"":objects[0].toString());
			mealRecordView.setIdNumber(objects[1]==null?"":objects[1].toString());
			mealRecordView.setSex(objects[2]==null?"":objects[2].toString());
			mealRecordView.setTotalMoney(objects[3]==null?0.0:Double.parseDouble(objects[3].toString()));
			mealRecordView.setPkConsumerAccount(objects[4]==null?0:Long.parseLong(objects[4].toString()));
			mealRecordView.setPkPersonalInfo(objects[5]==null?0:Long.parseLong(objects[5].toString()));
			viewList.add(mealRecordView);
		}
		return viewList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MealRecordDetailView> queryOnePerson(Date startDate, Date endDate, Long pkTravelAccount) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT bp.`name` as personName,bp.idNumber,bp.sex,money,cmr.count,cd.`name` as dishName,ccaf.operateDate ");                              
		sql.append(" ,bc.cardNumber, ccaf.pkTravelAccountFlow FROM    cate_travelaccountflow ccaf                  ");
		sql.append(" LEFT JOIN cate_travelaccount cca ON cca.pkTravelAccount=ccaf.pkTravelAccount                 ");
		sql.append(" LEFT JOIN travel_roompersonalInfo trp ON trp.pkRoomPersonalInfo = cca.pkRoomPersonalInfo           ");
		sql.append(" LEFT JOIN bd_personalinfo bp ON bp.pkPersonalInfo = trp.pkPersonalInfo                             ");
		sql.append(" LEFT JOIN cate_mealRecord cmr ON cmr.pkTravelAccountFlow = ccaf.pkTravelAccountFlow            ");
		sql.append(" LEFT JOIN cate_dish cd ON cd.pkDish = cmr.PkDish                                                   ");
		//sql.append(" LEFT JOIN cate_consumecard cc ON cc.pkCardManagement = trp.pkConsumeCard                            ");
		sql.append(" LEFT JOIN bd_cardmanagement bc ON trp.pkConsumeCard = bc.pkCardManagement                            ");
		
		sql.append(" WHERE ccaf.operateDate BETWEEN :startDate and :endDate		                                    ");
		sql.append(" AND 	cca.pkTravelAccount=:pkTravelAccount AND ccaf.consumeType='"+TravelAccountFlow.ConsumeType.Consume+"' ");
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("startDate", startDate).
													setParameter("endDate", endDate).
													setParameter("pkTravelAccount", pkTravelAccount)
													.list();
		TravelAccountFlow travelAccountFlow = new TravelAccountFlow();
		PropertyUtils.setProperty(travelAccountFlow, "travelAccount.pkTravelAccount", pkTravelAccount);
		PropertyUtils.setProperty(travelAccountFlow, "operateDate", startDate);
		PropertyUtils.setProperty(travelAccountFlow, "operateDateEnd", endDate);
		List<TravelAccountFlow> flowList = travelAccountFlowDao.query(travelAccountFlow);
		List<MealRecordDetailView> viewList = new ArrayList<>();
		for (int i = 0; i < flowList.size(); i++) {
			List<DishView> dishes = new ArrayList<>();
			MealRecordDetailView mealRecordDetailView = new MealRecordDetailView();
			int count = 0;
			for (int j = 0; j < objList.size(); j++) {
				Object[] objects = objList.get(j);
				if(flowList.get(i).getPkTravelAccountFlow()==Long.parseLong(objects[8].toString())){
					count++;
					DishView dishView = new DishView();
					String str = "";
					if(objects[5]!=null){
						str = objects[5].toString() + (objects[4]==null?"":"(x" + objects[4].toString()+")");
					}
					dishView.setName(str);
					dishes.add(dishView);
					if(count==1){
						mealRecordDetailView.setMoney(objects[3]==null?0.0:Double.parseDouble(objects[3].toString()));
						mealRecordDetailView.setName(objects[0]==null?"":objects[0].toString());
						mealRecordDetailView.setNumber(objects[7]==null?"":objects[7].toString());
						mealRecordDetailView.setOperateDate(objects[6]==null?null:(Date)objects[6]);
					}
				}
				if(j==objList.size()-1){
					mealRecordDetailView.setDishes(dishes);
				}
			}
			if(mealRecordDetailView.getOperateDate()!=null){
				viewList.add(mealRecordDetailView);
			}
		}
		
		return viewList;
	}
}
